from flask import Flask, render_template, jsonify, make_response
import mysql.connector
from influxdb import InfluxDBClient
from datetime import datetime
import json

app = Flask(__name__)

# MySQL 数据库连接配置
db_config = {
    'user': 'root',
    'password': '111111',
    'host': '192.168.43.98',
    'database': 'scrapy_db'
}

# InfluxDB 连接配置
influx_config = {
    'host': '192.168.43.98',
    'port': 8086,
    'username': '',  # 填入您的 InfluxDB 用户名
    'password': '',  # 填入您的 InfluxDB 密码
    'database': 'scrapy_status'  # 填入您的 InfluxDB 数据库名
}


def get_data_from_influxdb(measurement):
    client = InfluxDBClient(**influx_config)
    query = f'SELECT time, bytes, count FROM "{measurement}" ORDER BY time DESC LIMIT 10'
    result = client.query(query)
    recent_data = []
    for point in result.get_points():
        # 将 InfluxDB 返回的时间戳转换为 JavaScript 可接受的时间格式
        time_js = (datetime.fromisoformat(point['time'].rstrip('Z')) - datetime(1970, 1, 1)).total_seconds()
        recent_data.append({
            'time': time_js,  # 存储转换后的 Unix 时间戳（秒）
            'bytes': point.get('bytes', 0),
            'count': point.get('count', 0),
            'node_id': point.get('node_id', 0),
        })
    client.close()
    return recent_data


# 添加一个新的路由来返回实时数据的JSON
@app.route('/get-real-time-data')
def get_real_time_data():
    bytes_data = get_data_from_influxdb('bytes_downloaded_per_10_seconds')
    responses_data = get_data_from_influxdb('responses_per_10_seconds')

    # 准备数据结构
    data = {
        'labels': [entry['time'] for entry in bytes_data],
        'datasets': [
            {'label': 'Bytes Downloaded per 10 seconds', 'data': [entry['bytes'] for entry in bytes_data]},
            {'label': 'Responses per 10 seconds', 'data': [entry['count'] for entry in responses_data]}
        ]
    }
    return make_response(jsonify(data), 200)


@app.route('/monitor')
def monitor():
    bytes_data = get_data_from_influxdb('bytes_downloaded_per_10_seconds')
    responses_data = get_data_from_influxdb('responses_per_10_seconds')

    # 合并数据
    combined_data = []
    for bytes_entry, responses_entry in zip(bytes_data, responses_data):
        combined_entry = {
            'time': datetime.fromtimestamp(bytes_entry['time']),  # 将 Unix 时间戳转换为 datetime 对象
            'bytes': bytes_entry['bytes'],
            'count': responses_entry['count']
        }
        combined_data.append(combined_entry)

    # 准备数据结构供前端绘制图表
    # labels = [entry['time'].strftime('%Y-%m-%dT%H:%M:%SZ') for entry in combined_data]
    # 格式化时间戳为字符串
    labels = ['1','2','3','4','5','6','7','8','9','10']
    datasets = [{
        'label': 'Bytes Downloaded per 10 seconds',
        'data': [entry['bytes'] for entry in combined_data]
    }]
    datasets_req = [{
        'label': 'Responses per 10 seconds',
        'data': [entry['count'] for entry in combined_data]
    }]

    return render_template('monitor.html', labels=labels, datasets=datasets,datasets_req=datasets_req)

@app.route('/')
@app.route('/index')
def home():
    # MySQL 数据库操作
    con = mysql.connector.connect(**db_config)
    cur = con.cursor()

    # 获取经典电影数量
    cur.execute('SELECT COUNT(*) FROM comment')
    movie_count = cur.fetchone()[0]

    # 获取评分统计数量
    cur.execute('SELECT COUNT(DISTINCT movie_rate) FROM comment')
    score_count = cur.fetchone()[0]

    # 获取词汇统计数量
    cur.execute('SELECT comment_content FROM comment')
    data = cur.fetchall()
    text = ''.join([item[0] for item in data if item[0]])
    word_count = len(text.split())

    cur.close()
    con.close()

    # InfluxDB 数据库操作
    influx_data = None

    # 渲染模板并传递数据
    return render_template('index.html', movie_count=movie_count, score_count=score_count, word_count=word_count,
                           team_members=5, influx_data=influx_data)


@app.route('/movie')
def movie():
    datalist = []
    con = mysql.connector.connect(**db_config)
    cur = con.cursor()
    sql = "SELECT * FROM comment"
    cur.execute(sql)
    datalist = cur.fetchall()
    cur.close()
    con.close()
    return render_template('movie.html', movies=datalist)


@app.route('/score')
def score():
    score = []
    num = []
    con = mysql.connector.connect(**db_config)
    cur = con.cursor()
    sql = "SELECT movie_rate, COUNT(movie_rate) FROM comment GROUP BY movie_rate"
    cur.execute(sql)
    data = cur.fetchall()
    for item in data:
        score.append(str(item[0]))
        num.append(item[1])
    cur.close()
    con.close()
    return render_template('score.html', score=score, num=num)

# 添加另一个新的路由来返回实时数据的JSON
@app.route('/get-real-time-data-v2')
def get_real_time_data_v2():
    bytes_data = get_data_from_influxdb('bytes_downloaded_per_10_seconds')
    responses_data = get_data_from_influxdb('responses_per_10_seconds')

    # 准备数据结构
    data = {
        # 'labels': [entry['time'] for entry in bytes_data],
        'labels': ['1','2','3','4','5','6','7','8','9','10'],
        'datasets': [
            {'label': 'Bytes Downloaded per 10 seconds', 'data': [entry['bytes'] for entry in bytes_data]}],}
    return make_response(jsonify(data), 200)

@app.route('/get-real-time-data-v1')
def get_real_time_data_v1():
    bytes_data = get_data_from_influxdb('bytes_downloaded_per_10_seconds')
    responses_data = get_data_from_influxdb('responses_per_10_seconds')

    # 准备数据结构
    data = {
        # 'labels': [entry['time'] for entry in bytes_data],
        'labels': ['1','2','3','4','5','6','7','8','9','10'],
        'datasets':[
        {'label': 'Responses per 10 seconds', 'data': [entry['count'] for entry in responses_data]}
    ]}
    return make_response(jsonify(data), 200)

@app.route('/word')
def word():
    return render_template('word.html')


if __name__ == '__main__':
    app.run(debug=True)
